#!/bin/sh
#
#  This shell script gives you some examples how to use 
#  the SQLite3 (sqlite) library.
#

# Here we include libinclude.sh.
. libinclude.sh

# Here we import the SQLite3 library.
import sqlite

# We switch on the verbose mode, to get more output ;)
sqlite_VERBOSE='1'

# We set the working SQLite3 db file.
sqlite_set_db example.sqlite

# Let's get some information about the data base...
echo -n "-> example.sqlite contains following table(s): "
sqlite_get_tables
echo -n "-> the table 'users' contains the following columns: "
sqlite_get_columns users
echo '--------------------------------'
echo ''

# We define a function which access to the variables $[PREFIX]_[COLUMN].
MyFunc1 () {
	echo ''
	echo "*** $sql_login ***"
	echo "uid: $sql_uid  pwd: $sql_password"
	echo "gid: $sql_gid  com: $sql_comment"
}
MyFunc2 () {
	echo "- $sql_login has uid: $sql_uid"
}

# Here we process each data row in the SQLite table (users) with MyFunc:
echo "Processing 'users' with MyFunc1() reads:"
sqlite_func users MyFunc1 sql 
echo '--------------------------------'
echo ''

# Here we process only a selction of the data,
# namely those rows where gid=106!
column='gid'
value='106'
echo "*** Group $column=$value ***"
sqlite_func_where users MyFunc2 sql "$column='$value'"
echo '--------------------------------'
echo ''

# Here we just 'received' the variables from 'John':
column='login'
value='John'
sqlite_row users "$column='$value'" sql
# and access to them:
echo "User $value ($sql_comment) has uid=$sql_uid !"
echo '--------------------------------'
echo ''

# We add a new dataset for 'Max':
data_login='Max'
data_uid='205'
data_password='default'
data_gid='110'
data_comment='trainee'
echo 'New user entry:'
sqlite_insert users data
sqlite_row users "login='$data_login'" sql
MyFunc1
echo '--------------------------------'
echo ''

# Now we change his password entry:
echo "We change $data_login's password:"
sqlite_update users password 'yHo01xp!' "login='$data_login'"
sqlite_row users "login='$data_login'" sql
MyFunc1
echo '--------------------------------'
echo ''


# To simply save/access options and parameter, we included a set of
# function to support a table with just two columns like 'Name' & 'Value'
# to store variables.

# To create Config-Variable-Table (cvt) with the name 'conf', we use...
echo "Create Config-Variable-Table (cvt)."
sqlite_cvt_create conf
echo '--------------------------------'
echo ''

# Save data in that table:
sqlite_cvt_set conf 'COLOR' 'blue'
sqlite_cvt_set conf 'SHAPE' 'triangle'
sqlite_cvt_set conf 'SURFACE' 'rough'
echo '--------------------------------'
echo ''

# Read data, an use them:
sqlite_cvt_read conf cfg
echo "There is a $cfg_COLOR $cfg_SHAPE with a $cfg_SURFACE surface."
echo '--------------------------------'
echo ''

# To reset a value we use the same function than above:
sqlite_cvt_set conf 'COLOR' 'red'
sqlite_cvt_set conf 'SHAPE' 'square'
sqlite_cvt_read conf cfg
echo "There is a $cfg_COLOR $cfg_SHAPE with a $cfg_SURFACE surface."
echo '--------------------------------'
echo ''

# We revert the SQLite3 db file.
cp example.sqlite.org example.sqlite

